package team.niit.dao.impl;

import cn.hutool.core.convert.Convert;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.Query;
import team.niit.dao.CourseDAO;
import team.niit.entity.CourseEntity;
import team.niit.entity.StudentCourseEntity;
import team.niit.entity.StudentEntity;
import team.niit.util.SessionFactoryUtil;

import java.io.Serializable;
import java.util.List;

/**
 * @Author: HAPPY
 * @Project_name: JSF
 * @Package_name: team.niit.dao.impl
 * @Date: 2021/10/26 20:09
 * Explain:
 */
public class CourseDAOImpl implements CourseDAO {
    private Session session;
    private Transaction tx;

    @Override
    public boolean insertCourse(CourseEntity courseEntity) {
        try {
            session = SessionFactoryUtil.getSessionFactory().openSession();
            tx = session.beginTransaction();

            session.save(courseEntity);
            tx.commit();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            if (null != tx) {
                tx.rollback();
            }
        } finally {
            session.close();
        }
        return false;
    }

    @Override
    public boolean deleteCourseById(String id) {
        Session session = SessionFactoryUtil.getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        String sql = "delete from course where id = ?";
        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter(1, id);
        query.executeUpdate();
        transaction.commit();
        return true;
    }

    @Override
    public List<CourseEntity> selectOptionalCoursesList() {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        String sqlGetAllOptionalCourse = "from team.niit.entity.CourseEntity course where course.quota > 0";
        Query query = session.createQuery(sqlGetAllOptionalCourse);

        return query.list();
    }

    @Override
    public List<CourseEntity> pagingQuery(int size, int pageNumber) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        Query query = session.createQuery("from team.niit.entity.CourseEntity");
        int resSize = query.list().size();
        int firstRes;
        if (pageNumber > 0) {
            firstRes = size * pageNumber;
        } else {
            firstRes = 0;
        }

        // 如果当前查询条数大于总条数
        if (size > resSize) {
            firstRes = 0;
            size = resSize;
        } else {
            // 如果当前的开始位置加上要查询的数量大于总的
            if (size + firstRes > resSize) {
                size = resSize - firstRes;
            }
        }
        query.setFirstResult(firstRes);
        query.setMaxResults(size);
        return query.list();
    }

    @Override
    public int count() {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        SQLQuery sqlQuery = session.createSQLQuery("select count(*) from course");
        int res = Convert.toInt(sqlQuery.list().get(0));
        return res;
    }

    @Override
    public boolean isExist(String courseId, String studentId) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        SQLQuery query = session.createSQLQuery("select count(*) from student_course where student_id = ? and course_id = ?");
        query.setParameter(1, studentId);
        query.setParameter(2, courseId);
        Integer res = Convert.toInt(query.list().get(0));
        return res > 0;
    }

    @Override
    public boolean selectCourse(String courseId, String studentId) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        // 修改课程余额
        String update = "update from team.niit.entity.CourseEntity course set course.quota = course.quota - 1 where course.id = ?0";
        Query modifyQuota = session.createQuery(update);
        modifyQuota.setParameter(0, courseId);
        modifyQuota.executeUpdate();

        // 像学生课表中插入选课数据
        Serializable id = session.save(new StudentCourseEntity().setStudentId(studentId).setCourseId(courseId));
        transaction.commit();
        if (id == null) {
            return false;
        }
        return true;
    }

    @Override
    public boolean dropCourse(String courseId, String studentId) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        // 修改课程余额
        String update = "update from team.niit.entity.CourseEntity course set course.quota = course.quota + 1 where course.id = ?0";
        Query modifyQuota = session.createQuery(update);
        modifyQuota.setParameter(0, courseId);
        modifyQuota.executeUpdate();

        String sql = "delete from student_course where course_id = ? and student_id = ?";
        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter(1, courseId);
        query.setParameter(2, studentId);
        query.executeUpdate();
        transaction.commit();
        return true;
    }

    @Override
    public CourseEntity[][] selectAllCourse(String id) {
        CourseEntity[][] courseEntities = new CourseEntity[5][8];
        // 课表旁边的文本信息
        courseEntities[0][7] = new CourseEntity().setName("第一节");
        courseEntities[1][7] = new CourseEntity().setName("第二节");
        courseEntities[2][7] = new CourseEntity().setName("第三节");
        courseEntities[3][7] = new CourseEntity().setName("第四节");
        courseEntities[4][7] = new CourseEntity().setName("第五节");

        session = SessionFactoryUtil.getSessionFactory().openSession();

        String selectCourseIdList = "select course_id from student_course where student_id = ?0";
        SQLQuery queryCourseIdList = session.createSQLQuery(selectCourseIdList);
        queryCourseIdList.setParameter(0, id);
        List<String> courseIdList = queryCourseIdList.list();

        String selectCourseList = "from team.niit.entity.CourseEntity course where course.id = ?0";
        for (String courseId : courseIdList) {
            Query queryCourseList = session.createQuery(selectCourseList);
            queryCourseList.setParameter(0, courseId);
            CourseEntity courseEntity = (CourseEntity) queryCourseList.list().get(0);
            int i = courseEntity.getNo() % 5; // 23 % 5 = 3;
            int j = courseEntity.getNo() / 5; // 23 / 5 = 4;
            courseEntities[i][j] = courseEntity;
        }

        return courseEntities;
    }

    @Override
    public List<CourseEntity> selectSelectedCourse(String id) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        String hql = "select * from course where course.id in (select course_id from student_course where student_id = ?)";
        SQLQuery query = session.createSQLQuery(hql);
        query.addEntity(CourseEntity.class);
        query.setParameter(1, id);
        return query.list();
    }

    @Override
    public int countCourse(String studentId) {
        session = SessionFactoryUtil.getSessionFactory().openSession();

        String hql = "select count(*) from student_course where student_id = ?";
        SQLQuery query = session.createSQLQuery(hql);
        query.setParameter(1, studentId);
        return Convert.toInt(query.list().get(0));
    }

    @Override
    public boolean isNull(String courseId, String studentId) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        SQLQuery query = session.createSQLQuery("select count(*) from course where id in(" +
                "select course_id from student_course where student_id = ?) and no in (select no from course where id = ?)");
        query.setParameter(1, studentId);
        query.setParameter(2, courseId);
        Integer res = Convert.toInt(query.list().get(0));
        return res == 0;
    }


    @Override
    public StudentEntity insertSelectedCourse(String oldCourseId, String newCourseId, String studentId) {
        return null;
    }

    @Override
    public boolean checkQuota(String courseId) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        SQLQuery query = session.createSQLQuery("select quota from course where id  = ?1");
        query.setParameter(1, courseId);
        Integer res = Convert.toInt(query.list().get(0));
        return res > 0;
    }

    /*
   管理员修改课程
    */
    @Override
    public boolean updateCourse(CourseEntity courseEntity) {
        try {
            session = SessionFactoryUtil.getSessionFactory().openSession();
            Transaction tx = session.beginTransaction();
            session.update(courseEntity);
            tx.commit();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            if (null != tx) {
                tx.rollback();
            }
        } finally {
            session.close();
        }
        return false;
    }

    /*
 管理员查询课程
    */
    @Override
    public CourseEntity selectById(String id) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        Query query = session.createQuery("from team.niit.entity.CourseEntity course where course.id = ?0");
        query.setParameter(0, id);
        if (query.list() == null || query.list().size() == 0) {
            return null;
        }
        return (CourseEntity) query.list().get(0);
    }


    public List<CourseEntity> queryStudentCourse(String studentId) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        session.beginTransaction();
        String hql = " select * from course where id in(select course_id from student_course where student_id = ?)";
        SQLQuery sqlQuery = session.createSQLQuery(hql);
        sqlQuery.addEntity(CourseEntity.class);
        sqlQuery.setParameter(1, studentId);
        return sqlQuery.list();
    }


    @Override
    public boolean insertStudentCourse(String studentId, String courseId) {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        //修改课程余额
        String update = "update from team.niit.entity.CourseEntity course set course.quota = course.quota - 1 where course.id = ?0";
        Query modifQuota = session.createQuery(update);
        modifQuota.setParameter(0, courseId);
        modifQuota.executeUpdate();

        Serializable id = session.save(new StudentCourseEntity().setStudentId(studentId).setCourseId(courseId));
        transaction.commit();
        if (id == null) {
            return false;
        }
        return true;
    }

    @Override
    public boolean deleteStudentCourse(String studentId, String courseId) {
        Session session = SessionFactoryUtil.getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();

        String update = "update from team.niit.entity.CourseEntity course set course.quota = course.quota + 1 where course.id = ?0";
        Query modifQuota = session.createQuery(update);
        modifQuota.setParameter(0, courseId);
        modifQuota.executeUpdate();

        String sql = "delete from student_course where course_id = ? and student_id = ?";
        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter(1, courseId);
        query.setParameter(2, studentId);
        query.executeUpdate();
        transaction.commit();
        return true;
    }

    @Override
    public List<CourseEntity> selectAll() {
        session = SessionFactoryUtil.getSessionFactory().openSession();
        return session.createQuery("from team.niit.entity.CourseEntity").list();
    }
}
